package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

/**
 * Servlet implementation class QueryTP
 */
//@WebServlet(description = "查詢測試計畫案", urlPatterns = { "/QueryTP" })
public class QueryTP extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public QueryTP() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("UTF-8");
		String q_tpID = request.getParameter("q_tpID");
		String q_tpName = request.getParameter("q_tpName");
		String q_cfromDate = request.getParameter("q_cfromDate");
		String q_ctoDate = request.getParameter("q_ctoDate");
		String q_ufromDate = request.getParameter("q_ufromDate");
		String q_utoDate = request.getParameter("q_utoDate");
		String[] owner = request.getParameter("tpOwnerList").split(",");
		String strSQL = "SELECT t1.tpid,t1.pid, t1.tpName, t1.comment, t1.createdDate, t1.lastUpdatedDate FROM activiti.tms_tp t1 WHERE t1.tpid=t1.tpid AND ";		

		if (owner.length > 0 && !"".equalsIgnoreCase(owner[0].trim()) ) {
			strSQL = "SELECT t1.tpid,t1.pid, t1.tpName, t1.comment, t1.createdDate, t1.lastUpdatedDate FROM activiti.tms_tp t1,activiti.tms_tpowner t2 WHERE t1.tpid=t2.tpid AND ";
			for (int i=0;i<owner.length;i++) {
				strSQL += "t2.userID='" + owner[i] + "' AND ";
			}
		}
		
		if (q_tpID != null && !q_tpID.trim().equals("")) {
			strSQL += "t1.tpid='" + q_tpID + "' AND ";
		} 
		
		if (q_tpName != null && !q_tpName.trim().equals("")) {
			strSQL += "t1.tpName='" + q_tpName + "' AND ";
		}
		
		if ((q_cfromDate != null && !q_cfromDate.trim().equals(""))&&(q_ctoDate != null && !q_ctoDate.trim().equals(""))) {			//兩個都有輸入
			strSQL += "t1.createdDate BETWEEN '" + q_cfromDate + "' AND '" + q_ctoDate +"' AND ";
		} else if ((q_cfromDate != null && !q_cfromDate.trim().equals(""))&&!(q_ctoDate != null && !q_ctoDate.trim().equals(""))) {	//僅有起
			strSQL += "t1.createdDate BETWEEN '" + q_cfromDate + "' AND '" + "2020-12-31' AND ";
		} else if (!(q_cfromDate != null && !q_cfromDate.trim().equals(""))&&(q_ctoDate != null && !q_ctoDate.trim().equals(""))) {	//僅有迄
			strSQL += "t1.createdDate BETWEEN '" + "1999-12-31" + "' AND '" + q_ctoDate +"' AND ";
		}
		
		if ((q_ufromDate != null && !q_ufromDate.trim().equals(""))&&(q_utoDate != null && !q_utoDate.trim().equals(""))) {			//兩個都有輸入
			strSQL += "t1.lastUpdatedDate BETWEEN '" + q_ufromDate + "' AND '" + q_utoDate +"'";;
		} else if ((q_ufromDate != null && !q_ufromDate.trim().equals(""))&&!(q_utoDate != null && !q_utoDate.trim().equals(""))) {	//僅有起
			strSQL += "t1.lastUpdatedDate BETWEEN '" + q_ufromDate + "' AND '" + "2020-12-31'";
		} else if (!(q_ufromDate != null && !q_ufromDate.trim().equals(""))&&(q_utoDate != null && !q_utoDate.trim().equals(""))) {	//僅有迄
			strSQL += "t1.lastUpdatedDate BETWEEN '" + "1999-12-31" + "' AND '" + q_utoDate +"'";
		} else {
			strSQL = strSQL.substring(0, strSQL.length()-4);		//皆無，將尾巴的 AND 拿掉
		}
		
		Connection conn = null;
		Widgets wid = new Widgets();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<TestPlan> testPlans = new ArrayList<TestPlan>();
		String tt = "";
		
		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			while (rs.next()) {
				ArrayList<User> users = new ArrayList<User>();
				TestPlan testPlan = new TestPlan();
				testPlan.setTpid(rs.getInt("tpid"));
				testPlan.setTpName(rs.getString("tpName"));
				testPlan.setComment(rs.getString("comment"));
				testPlan.setCreateDate(rs.getDate("createdDate"));
				testPlan.setLastUpdateDate(rs.getDate("lastUpdatedDate"));
				testPlan.setTpOwner(users);
				testPlans.add(testPlan);
			}
			if (testPlans.size() > 0) {
				tt = new Gson().toJson(testPlans);
				tt = tt.substring(1, tt.length());
				tt = "{\"sqlData\":["+tt+",\"strSQL\":\""+strSQL+"\"}";
			} else {
				tt = new Gson().toJson(testPlans);
				tt = tt.replace("[", "{\"sqlData\":[")+ ",\"strSQL\":[{\"result\":\"No Result\"}]}";
			}
		} catch (SQLException e) {
			tt = "{\"errorMsg\":\""+wid.keepErrorMes(conn, ps, e, "", true)+"\"}";
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				tt = "{\"errorMsg\":\""+e.toString()+"\"}";
			}
		}
		
		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(tt);
		
	}
}
